package com.education.ai.mapper.impl;

import com.education.ai.entity.ClassVO;
import com.education.ai.mapper.ClassMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ClassMapper接口的实现类
 */
@Repository
public class ClassMapperImpl implements ClassMapper {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<ClassVO> getTeacherClasses(String teacherId) {
        try {
            System.out.println("正在查询教师ID为" + teacherId + "的班级列表");
            
            // 首先查询教师信息
            String teacherSql = "SELECT t.id, t.name, t.teacher_id FROM teachers t WHERE t.teacher_id = ?";
            System.out.println("执行教师查询SQL: " + teacherSql);
            List<Map<String, Object>> teachers = jdbcTemplate.queryForList(teacherSql, teacherId);
            
            if (teachers.isEmpty()) {
                System.out.println("未找到教师ID为" + teacherId + "的教师信息");
                return new ArrayList<>();
            }
            
            Map<String, Object> teacher = teachers.get(0);
            String teacherName = (String) teacher.get("name");
            System.out.println("找到教师信息: ID=" + teacher.get("id") + ", 姓名=" + teacherName);
            
            // 查询班级信息
            String sql = "SELECT c.id, c.class_name as className, c.grade " +
                    "FROM classes c " +
                    "WHERE c.chinese = ? OR c.math = ? OR c.english = ? OR " +
                    "c.physics = ? OR c.chemistry = ? OR c.biology = ? OR " +
                    "c.history = ? OR c.geography = ? OR c.politics = ? " +
                    "ORDER BY c.grade, c.class_name";
            
            System.out.println("执行班级查询SQL: " + sql);
            System.out.println("查询参数: " + teacherName);
            
            List<ClassVO> result = jdbcTemplate.query(sql, 
                (rs, rowNum) -> new ClassVO(
                    rs.getLong("id"),
                    rs.getString("className"),
                    rs.getString("grade")
                ),
                teacherName, teacherName, teacherName, 
                teacherName, teacherName, teacherName, 
                teacherName, teacherName, teacherName
            );
            
            System.out.println("查询结果: 返回了" + result.size() + "条记录");
            for (ClassVO classVO : result) {
                System.out.println("班级: ID=" + classVO.getId() + ", 名称=" + classVO.getClassName() + ", 年级=" + classVO.getGrade());
            }
            
            return result;
        } catch (Exception e) {
            // 发生错误时返回空列表
            System.out.println("查询班级列表出错: " + e.getMessage());
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    @Override
    public List<Map<String, Object>> getStudentsByClassId(Long classId, int offset, int limit) {
        try {
            // 先查询学生的基本信息（不包含成绩）
            String sql = "SELECT s.id, s.name, s.student_id as studentId, s.profile " +
                    "FROM students s " +
                    "WHERE s.class_id = ? " +
                    "ORDER BY s.id " +
                    "LIMIT ?, ?";
            
            System.out.println("执行查询学生SQL: " + sql);
            System.out.println("查询参数: classId=" + classId + ", offset=" + offset + ", limit=" + limit);
            
            List<Map<String, Object>> students = jdbcTemplate.queryForList(sql, classId, offset, limit);
            System.out.println("查询结果: 返回了" + students.size() + "条学生记录");
            
            // 如果没有找到学生，尝试使用不含profile字段的查询
            if (students.isEmpty()) {
                System.out.println("未找到学生，尝试使用不含profile字段的查询");
                String fallbackSql = "SELECT s.id, s.name, s.student_id as studentId " +
                        "FROM students s " +
                        "WHERE s.class_id = ? " +
                        "ORDER BY s.id " +
                        "LIMIT ?, ?";
                students = jdbcTemplate.queryForList(fallbackSql, classId, offset, limit);
                System.out.println("备用查询结果: 返回了" + students.size() + "条学生记录");
                
                // 如果还是没有找到学生，尝试通过班级名称查询
                if (students.isEmpty()) {
                    System.out.println("通过class_id仍未找到学生，尝试通过班级名称查询");
                    
                    // 根据班级ID获取班级名称
                    String className = null;
                    if (classId == 1) {
                        className = "高一(1)班";
                    } else if (classId == 2) {
                        className = "高一(2)班";
                    } else if (classId == 3) {
                        className = "高二(1)班";
                    }
                    
                    if (className != null) {
                        System.out.println("尝试使用班级名称: " + className);
                        
                        // 通过class_name字段查询学生
                        String classNameSql = "SELECT s.id, s.name, s.student_id as studentId " +
                                "FROM students s " +
                                "WHERE s.class_name = ? " +
                                "ORDER BY s.id " +
                                "LIMIT ?, ?";
                        students = jdbcTemplate.queryForList(classNameSql, className, offset, limit);
                        System.out.println("通过班级名称查询结果: 返回了" + students.size() + "条学生记录");
                    }
                }
                
                // 为每个学生添加一个默认的profile字段
                for (Map<String, Object> student : students) {
                    student.put("profile", "该学生暂无个人档案信息");
                }
            }
            
            // 为每个学生查询各科的平均成绩
            for (Map<String, Object> student : students) {
                String studentId = (String) student.get("studentId");
                
                try {
                    // 首先检查学生是否有考试记录
                    String checkRecordSql = "SELECT COUNT(*) FROM exam_records WHERE student_id = ?";
                    Integer recordCount = jdbcTemplate.queryForObject(checkRecordSql, Integer.class, studentId);
                    System.out.println("学生 " + studentId + " 有 " + recordCount + " 条考试记录");
                    
                    if (recordCount > 0) {
                        // 查询考试记录详情（仅用于日志调试）
                        String examDetailSql = "SELECT * FROM exam_records WHERE student_id = ? LIMIT 2";
                        List<Map<String, Object>> examDetails = jdbcTemplate.queryForList(examDetailSql, studentId);
                        System.out.println("学生 " + studentId + " 的考试记录示例: " + examDetails);
                        
                        // 查询平均成绩
                        String avgScoreSql = "SELECT " +
                                "COALESCE(AVG(chinese), 0) as chinese, " +
                                "COALESCE(AVG(math), 0) as math, " +
                                "COALESCE(AVG(english), 0) as english, " +
                                "COALESCE(AVG(politics), 0) as politics, " +
                                "COALESCE(AVG(history), 0) as history, " +
                                "COALESCE(AVG(geography), 0) as geography, " +
                                "COALESCE(AVG(physics), 0) as physics, " +
                                "COALESCE(AVG(chemistry), 0) as chemistry, " +
                                "COALESCE(AVG(biology), 0) as biology " +
                                "FROM exam_records " +
                                "WHERE student_id = ?";
                        
                        Map<String, Object> avgScores = jdbcTemplate.queryForMap(avgScoreSql, studentId);
                        System.out.println("学生 " + studentId + " 的平均成绩: " + avgScores);
                        
                        // 将平均成绩添加到学生信息中，确保没有null值
                        for (String subject : new String[]{"chinese", "math", "english", "politics", "history", 
                                                          "geography", "physics", "chemistry", "biology"}) {
                            Object score = avgScores.get(subject);
                            // 如果成绩为null或不是数字，则设为0
                            if (score == null) {
                                student.put(subject, 0.0);
                                System.out.println("学生 " + studentId + " 的 " + subject + " 成绩为null");
                            } else if (!(score instanceof Number)) {
                                student.put(subject, 0.0);
                                System.out.println("学生 " + studentId + " 的 " + subject + " 成绩不是数字: " + score);
                            } else {
                                double scoreValue = ((Number) score).doubleValue();
                                student.put(subject, scoreValue);
                                System.out.println("学生 " + studentId + " 的 " + subject + " 成绩: " + scoreValue);
                            }
                        }
                    } else {
                        // 添加默认的零分成绩
                        student.put("chinese", 0.0);
                        student.put("math", 0.0);
                        student.put("english", 0.0);
                        student.put("politics", 0.0);
                        student.put("history", 0.0);
                        student.put("geography", 0.0);
                        student.put("physics", 0.0);
                        student.put("chemistry", 0.0);
                        student.put("biology", 0.0);
                        System.out.println("学生 " + studentId + " 没有考试记录，使用默认成绩0");
                    }
                } catch (Exception e) {
                    System.out.println("查询学生 " + studentId + " 的平均成绩失败: " + e.getMessage());
                    e.printStackTrace();
                    // 如果查询失败，添加默认的零分成绩
                    student.put("chinese", 0.0);
                    student.put("math", 0.0);
                    student.put("english", 0.0);
                    student.put("politics", 0.0);
                    student.put("history", 0.0);
                    student.put("geography", 0.0);
                    student.put("physics", 0.0);
                    student.put("chemistry", 0.0);
                    student.put("biology", 0.0);
                }
            }
            
            return students;
        } catch (Exception e) {
            System.out.println("查询学生列表出错: " + e.getMessage());
            e.printStackTrace();
            
            // 出错时返回空列表
            return new ArrayList<>();
        }
    }

    @Override
    public int countStudentsByClassId(Long classId) {
        try {
            String sql = "SELECT COUNT(*) FROM students WHERE class_id = ?";
            int count = jdbcTemplate.queryForObject(sql, Integer.class, classId);
            
            // 如果没有找到学生，尝试通过班级名称查询
            if (count == 0) {
                // 根据班级ID获取班级名称
                String className = null;
                if (classId == 1) {
                    className = "高一(1)班";
                } else if (classId == 2) {
                    className = "高一(2)班";
                } else if (classId == 3) {
                    className = "高二(1)班";
                }
                
                if (className != null) {
                    System.out.println("尝试使用班级名称统计学生数量: " + className);
                    String classNameSql = "SELECT COUNT(*) FROM students WHERE class_name = ?";
                    count = jdbcTemplate.queryForObject(classNameSql, Integer.class, className);
                    System.out.println("通过班级名称统计结果: " + count + "名学生");
                }
            }
            
            return count;
        } catch (Exception e) {
            // 发生错误时返回0
            System.out.println("查询学生总数出错: " + e.getMessage());
            e.printStackTrace();
            return 0;
        }
    }
} 